﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Types;

namespace Server
{
    public class DatabaseManager
    {
        private static DatabaseManager instance;
        private DataClassesDataContext db;

        public static DatabaseManager Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new DatabaseManager();
                }
                return instance;
            }
        }

        private DatabaseManager()
        {
            db = new DataClassesDataContext(global::Server.Properties.Settings.Default.groups_mosheronenConnectionString);
        }

        public bool InsertNewUser(string username, string password, string firstName, string lastName, string id, int year)
        {
            try
            {
                var query = from user in db.Users
                            where user.Username == username
                            select user;
                if (query.Count() > 0)
                {
                    return false;
                }
                db.Users.InsertOnSubmit(new User()
                {
                    Username = username,
                    Password = password,
                    FirstName = firstName,
                    LastName = lastName,
                    Id = id,
                    Year = year,
                });
                db.SubmitChanges();
                return true;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public bool IsLegalUser(string username, string password)
        {
            try
            {
                var query = from user in db.Users
                            where user.Username == username && user.Password == password
                            select user;
                return query.Count() == 1;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public bool InsertNewQuestion(int id, string summary, string filePath, DateTime date, string creatorName, int courseId)
        {
            try
            {
                var query = from question in db.Questions
                            where question.Id == id
                            select question;
                if (query.Count() > 0)
                {
                    return false;
                }
                db.Questions.InsertOnSubmit(new Question()
                {
                    Id = id,
                    Summary = summary,
                    FilePath = filePath,
                    Date = date,
                    CreatorName = creatorName,
                    CourseId = courseId,
                });
                db.SubmitChanges();
                return true;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public bool InsertNewAnswer(int id, int questionId, string summary, string filePath, DateTime date)
        {
            try
            {
                var query = from answer in db.Answers
                            where answer.Id == id
                            select answer;
                if (query.Count() > 0)
                {
                    return false;
                }
                db.Answers.InsertOnSubmit(new Answer()
                {
                    Id = id,
                    QuestionId = questionId,
                    Summary = summary,
                    FilePath = filePath,
                    Date = date,
                });
                db.SubmitChanges();
                return true;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public int GetQuestionId()
        {
            int questionId = 0;

            try
            {
                var query = from questions in db.Questions
                            select questions.Id;
                try
                {
                    questionId = query.Max();
                }
                catch (System.InvalidOperationException)
                {
                    return 0;
                }
                return questionId;
            }
            catch (System.ArgumentNullException)
            {
                return -1;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return -1;
            }
        }

        public int GetAnswerId()
        {
            int answerId = 0;

            try
            {
                var query = from answers in db.Answers
                            select answers.Id;
                try
                {
                    answerId = query.Max();
                }
                catch (System.InvalidOperationException)
                {
                    return 0;
                }
                return answerId;
            }
            catch (System.ArgumentNullException)
            {
                return -1;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return -1;
            }
        }

        public int GetNumberOfAnswers()
        {
            try
            {
                var query = from answers in db.Answers
                            select answers;
                return query.Count();
            }
            catch (System.ArgumentNullException)
            {
                return -1;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return -1;
            }
        }

        public List<CourseType> GetCourseList(string username)
        {
            try
            {
                var year = (from user in db.Users
                           where user.Username == username
                           select user.Year).Single();
                int intYear = year;
                var courses = from course in db.Courses
                              where course.Year == intYear
                              select course;
                List<CourseType> courseList = new List<CourseType>();
                foreach (var item in courses)
                {
                    courseList.Add(new CourseType(item.Id, item.Name, item.Code, item.Year));
                }
                return courseList;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.InvalidOperationException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }

        public List<QuestionType> GetQuestionList(string username)
        {
            try
            {
                var year = (from user in db.Users
                           where user.Username == username
                           select user.Year).Single();
                int intYear = year;
                var courses = from course in db.Courses
                              where course.Year == intYear
                              select course;
                List<QuestionType> questionList = new List<QuestionType>();
                foreach (var course in courses)
                {
                    var questions = from question in db.Questions
                                    where question.CourseId == course.Id
                                    select question;
                    foreach (var question in questions)
                    {
                        questionList.Add(new QuestionType(question.Id, question.Summary, question.FilePath, question.CreatorName, question.CourseId));
                    }
                }
                return questionList;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.InvalidOperationException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }

        public List<QuestionType> GetMyQuestionList(string username)
        {
            try
            {
                var year = (from user in db.Users
                           where user.Username == username
                           select user.Year).Single();
                int intYear = year;
                var courses = from course in db.Courses
                              where course.Year == intYear
                              select course;
                List<QuestionType> questionList = new List<QuestionType>();
                foreach (var course in courses)
                {
                    var questions = from question in db.Questions
                                    where question.CourseId == course.Id && question.CreatorName == username
                                    select question;
                    foreach (var question in questions)
                    {
                        questionList.Add(new QuestionType(question.Id, question.Summary, question.FilePath, question.CreatorName, question.CourseId));
                    }
                }
                return questionList;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.InvalidOperationException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }

        public List<AnswerType> GetAnswerList(QuestionType question)
        {
            try
            {
                var answers = from answer in db.Answers
                              where answer.Question.Summary == question.summary
                              select answer;
                List<AnswerType> answerList = new List<AnswerType>();
                foreach (var answer in answers)
                {
                    answerList.Add(new AnswerType(answer.Id, answer.Summary, answer.QuestionId, answer.FilePath));
                }
                return answerList;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.InvalidOperationException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }

        public List<string> GetAnswerFilesPath(QuestionType question)
        {
            try
            {
                var answers = from a in db.Answers
                              where a.QuestionId == question.id
                              select a;
                List<string> answerFilesPath = new List<string>();
                foreach (var answer in answers)
                {
                    answerFilesPath.Add(answer.FilePath);
                }
                return answerFilesPath;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }

        public bool DeleteQuestion(QuestionType questionToDelete)
        {
            try
            {
                var query = (from question in db.Questions
                             where question.Id == questionToDelete.id
                             select question).Single();
                db.Questions.DeleteOnSubmit(query);
                db.SubmitChanges();
                return true;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.InvalidOperationException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public bool Search(ref List<QuestionType> searchResultQuestionList,
                           ref List<AnswerType> searchResultAnswerList,
                           string searchString,
                           string username)
        {
            try
            {
                var year = (from user in db.Users
                           where user.Username == username
                           select user.Year).Single();
                int intYear = year;
                var courses = from course in db.Courses
                              where course.Year == intYear
                              select course;
                foreach (var course in courses)
                {
                    var questions = from question in db.Questions
                                    where question.CourseId == course.Id
                                    select question;
                    foreach (var question in questions)
                    {
                        if (question.Summary.ToString().Contains(searchString))
                        {
                            searchResultQuestionList.Add(new QuestionType(question.Id, question.Summary, question.FilePath, question.CreatorName, question.CourseId));
                        }
                        var answeres = from answer in db.Answers
                                       where answer.Question == question
                                       select answer;
                        foreach (var answer in answeres)
                        {
                            if (answer.Summary.ToString().Contains(searchString))
                            {
                                searchResultAnswerList.Add(new AnswerType(answer.Id, answer.Summary, answer.QuestionId, answer.FilePath));
                            }
                        }
                    }
                }
                return true;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.InvalidOperationException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public bool SearchBetweenDates(ref List<QuestionType> searchResultQuestionList,
                                       ref List<AnswerType> searchResultAnswerList,
                                       string searchString,
                                       string username,
                                       DateTime startDate,
                                       DateTime endDate)
        {
            try
            {
                var year = (from user in db.Users
                           where user.Username == username
                           select user.Year).Single();
                int intYear = year;
                var courses = from course in db.Courses
                              where course.Year == intYear
                              select course;
                foreach (var course in courses)
                {
                    var questions = from question in db.Questions
                                    where question.CourseId == course.Id
                                    select question;
                    foreach (var question in questions)
                    {
                        if (question.Summary.ToString().Contains(searchString) && question.Date >= startDate && question.Date <= endDate)
                        {
                            searchResultQuestionList.Add(new QuestionType(question.Id, question.Summary, question.FilePath, question.CreatorName, question.CourseId));
                        }
                        var answeres = from answer in db.Answers
                                       where answer.Question == question
                                       select answer;
                        foreach (var answer in answeres)
                        {
                            if (answer.Summary.ToString().Contains(searchString) && answer.Date >= startDate && answer.Date <= endDate)
                            {
                                searchResultAnswerList.Add(new AnswerType(answer.Id, answer.Summary, answer.QuestionId, answer.FilePath));
                            }
                        }
                    }
                }
                return true;
            }
            catch (System.ArgumentNullException)
            {
                return false;
            }
            catch (System.InvalidOperationException)
            {
                return false;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
        }

        public string GetCourseName(int questionId)
        {
            try
            {
                var query = (from question in db.Questions
                             where question.Id == questionId
                             select question.Course).Single();
                return query.Name;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.InvalidOperationException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }

        public string GetQuestionSummary(int questionId)
        {
            try
            {
                var query = (from question in db.Questions
                            where question.Id == questionId
                            select question.Summary).Single();
                return query;
            }
            catch (System.ArgumentNullException)
            {
                return null;
            }
            catch (System.InvalidOperationException)
            {
                return null;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return null;
            }
        }
    }
}
